package top.psjj.news.dao;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import top.psjj.news.po.TComment;
import top.psjj.news.utils.DataSourceUtils;

import java.util.List;

/**
 * @Auther:小陈的Java
 */
public class CommentDao {
    private QueryRunner queryRunner = new QueryRunner(DataSourceUtils.getDataSource());
    public List<TComment> list(String s_bCommentDate, String s_aCommentDate)throws Exception {
        String sql = "select id ,news_id newsId,content,userIP userip,comment_date commentDate from t_comment where 1=1 ";
        if(s_bCommentDate!=null && !"".equals(s_bCommentDate)){
            sql+=" and comment_date>'"+s_bCommentDate+"'";
        }
        if(s_aCommentDate!=null && !"".equals(s_aCommentDate)){
            sql+=" and comment_date<'"+s_aCommentDate+"'";
        }
        sql+=" order by comment_date desc";
        List<TComment> result = queryRunner.query(sql, new BeanListHandler<TComment>(TComment.class));
        return result;
    }

    public List<TComment> page(String s_bCommentDate, String s_aCommentDate, int begin, int pageSize) throws Exception {
        //拼接sql语句
        String sql = "select id ,news_id newsId,content,userIP userip,comment_date commentDate from t_comment where 1=1 ";
        if(s_bCommentDate!=null && !"".equals(s_bCommentDate)){
            sql+=" and comment_date>'"+s_bCommentDate+"'";
        }
        if(s_aCommentDate!=null && !"".equals(s_aCommentDate)){
            sql+=" and comment_date<'"+s_aCommentDate+"'";
        }
        sql+=" order by comment_date desc";
        sql+=" limit "+begin+","+pageSize;
        List<TComment> result = queryRunner.query(sql, new BeanListHandler<TComment>(TComment.class));
        return result;
    }
    public void add(TComment comment) throws Exception{
        queryRunner.update("insert into t_comment(news_id,content,userIP,comment_date) values(?,?,?,?)",comment.getNewsId(),comment.getContent(),comment.getUserip(),comment.getCommentDate());

    }

/*    public static void main(String[] args) throws Exception {
        CommentDao commentDao = new CommentDao();
        for (int i = 1; i <=93; i++) {
            TComment comment = new TComment();
            comment.setNewsId(3);
            comment.setCommentDate(new Timestamp(System.currentTimeMillis()));
            comment.setContent("开的厉害"+i);
            comment.setUserip("127.0.0.1");
            commentDao.add(comment);
        }
    }*/

    public int count(String s_bCommentDate, String s_aCommentDate) throws Exception {
        //拼接sql语句
        String sql = "select count(*) from t_comment where 1=1 ";
        if(s_bCommentDate!=null && !"".equals(s_bCommentDate)){
            sql+=" and comment_date>'"+s_bCommentDate+"'";
        }
        if(s_aCommentDate!=null && !"".equals(s_aCommentDate)){
            sql+=" and comment_date<'"+s_aCommentDate+"'";
        }
        Long result = queryRunner.query(sql, new ScalarHandler<Long>());
        return result.intValue();
    }
}
